-- EXEC SP_LOAD_ALL_TRANSIT_LOTS_SEARCH 'Fab,Fab Option,Sort', '0', 0, '0', 0, 0
 
ALTER PROCEDURE SP_LOAD_ALL_TRANSIT_LOTS_SEARCH
(
 @PROCESS_TYPES		VARCHAR(200),
 @PROCESS_STEP_IDS	VARCHAR(200),
 @LOCATION_ID		INT,
 @NEXT_PROCESS		VARCHAR(100),
 @SHIP_TOVENDORID	INT	, --USEFUL WHNE VENDOR INTERFACE	
 @SEARCH_LOT_ID     INT		
 )
AS
BEGIN
	
	DECLARE @PART_NUMBER 	VARCHAR(50)
	DECLARE @PREVIOUS_LOC 	VARCHAR(50)
	DECLARE @SHIP_QTY 	INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @PS_TYPE_ID	INT 
	DECLARE @LOT_ID		BIGINT
	DECLARE @PROCESSTEP_ID INT
	DECLARE @DB_LOTNO     VARCHAR(10)
	DECLARE @DB_PROCESSSTEPID     INT
	DECLARE @VENDOR_CODE VARCHAR(10)
	DECLARE @GORSS_DIE_PER_WAFER INT
	DECLARE @GROSS_DIE INT
	DECLARE @SHIPPEDLOT_ID INT
	DECLARE @WW_YEAR VARCHAR(4)
	DECLARE @YEARLASTDIGIT VARCHAR(4)
	
	SET @WW_YEAR = DATEPART(YYYY, CURRENT_TIMESTAMP)+''
	
	SET @YEARLASTDIGIT = SUBSTRING(@WW_YEAR, len(@WW_YEAR), len(@WW_YEAR) )
	
	CREATE TABLE #RESULTS ( PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50),PS_TYPE VARCHAR(50),DATE_CODE VARCHAR(50),
				PREVIOUS_LOC VARCHAR(50),NEXT_LOC VARCHAR(100), NEXT_PROCESS VARCHAR(50), SHIP_DATE DATETIME, SHIP_QTY INT, GDPW INT, GROSS_DIE INT, PS_TYPE_ID INT, SHIPPEDLOT_ID INT,WAFER_ID VARCHAR(150), LOT_ID BIGINT, VENDOR_CODE VARCHAR(10), DB_LOTNO VARCHAR(10), DB_PROCESSSTEP_ID INT, BIN_QTY_STR VARCHAR(150) )

	DECLARE @TRANSITCURSOR CURSOR
	
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SET @QUERYSTRING = N'INSERT INTO #RESULTS SELECT PS.PROCESSSTEP_ID, PRO.PART_NUMBER,LT.LOT_NUMBER, LT.FAB_LOT_NUMBER,  PS.PS_TYPE, LT.LOT_DATE_CODE,
			 VEN.V_NAME , SL.SL_NEXTLOCATION, SL.SL_NEXT_PROCESS, SL.SHIP_DATE, SL.SHIP_QTY, NULL, NULL, NULL, SL.SHIPPEDLOT_ID ,SL.WAFER_ID,SL.LOT_ID, NULL, NULL, NULL, NULL 
			FROM LOT_TABLE LT, SHIPPED_LOT SL, PRODUCT PRO, PROCESS_STEP PS, LOCATION LOC, VENDOR VEN
			WHERE SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PRO.PROCESSSTEP_ID AND PRO.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			 AND SL.STATUS = ''TRANSIT'' AND 
			LT.LOCATION_ID = LOC.LOCATION_ID AND LOC.VENDOR_ID = VEN.VENDOR_ID AND SL.SHIP_STATUS <= 0'
	
	IF @PROCESS_TYPES IS NOT NULL AND @PROCESS_TYPES <> '0'
 	BEGIN
 		
		SET @QUERYSTRING = @QUERYSTRING + '  AND (( PS_TYPE IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @PROCESS_TYPES +''') ) AND SL_NEXT_PROCESS IS NULL )'
		
		SET @QUERYSTRING = @QUERYSTRING + ' OR ( SL_NEXT_PROCESS IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @NEXT_PROCESS +''') ) ) ) '
	END
	
 	IF ( @PROCESS_STEP_IDS IS NOT NULL AND @PROCESS_STEP_IDS <> '0'  )
 	BEGIN
		SET @QUERYSTRING = @QUERYSTRING +' AND PRO.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PROCESS_STEP_IDS +''') )'
	END
	IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
 	END
	IF ( @SHIP_TOVENDORID IS NOT NULL AND @SHIP_TOVENDORID <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND ( SL.SL_NEXTLOCATION IS NULL OR SL.SL_NEXTLOCATION = ( SELECT DISTINCT L_VENDOR_NAME FROM LOCATION WHERE VENDOR_ID = ' + CONVERT(VARCHAR,@SHIP_TOVENDORID) +') )'
 	END
	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID <> '0'  )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 	END
	--PRINT '@QUERYSTRING :- '+@QUERYSTRING

	EXEC (@QUERYSTRING)
	--SELECT *FROM #RESULTS

	DECLARE @WAFERS_TABLE	TABLE ( WAFER_ID INT, QTY_IN INT )
	DECLARE @QTY_AS_STRING	VARCHAR(200)
	DECLARE @WAFER_ID_STR	VARCHAR(100)
	DECLARE @SHIP_NEXT_PROCESS	VARCHAR(50)

	SET @TRANSITCURSOR = CURSOR FOR SELECT PROCESSSTEP_ID, PS_TYPE, PREVIOUS_LOC, SHIP_QTY, SHIPPEDLOT_ID, LOT_ID, NEXT_PROCESS FROM #RESULTS
		
	OPEN @TRANSITCURSOR
	FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID, @PS_TYPE, @PREVIOUS_LOC , @SHIP_QTY, @SHIPPEDLOT_ID, @LOT_ID, @SHIP_NEXT_PROCESS 

	WHILE @@FETCH_STATUS = 0
	BEGIN
		Select @PS_TYPE_ID = SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE Process_Type = @PS_TYPE
			
		SELECT @GORSS_DIE_PER_WAFER = ISNULL(Parameter_VALUE,0) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,0) WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'
		IF @GORSS_DIE_PER_WAFER IS NULL OR @GORSS_DIE_PER_WAFER = 0
		BEGIN
			SELECT @GORSS_DIE_PER_WAFER = ISNULL(Parameter_VALUE,0) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,0) WHERE PS_TYPE = 'Fab Option' ) AND Parameter_Key = 'Gross Die/Wafer'
		END
		
		IF @PS_TYPE = 'FAB' OR @PS_TYPE = 'Fab Option'
		BEGIN
			DECLARE @VENDOR_LOTNO INT
			DECLARE @LOT_NUMBER_AS_STR VARCHAR(10)

			SET @GROSS_DIE				= @SHIP_QTY * ISNULL(@GORSS_DIE_PER_WAFER,0)
			SELECT @VENDOR_CODE = V_code, @VENDOR_LOTNO = CONVERT(INT,ISNULL(V_LOTNO, '001') ) FROM VENDOR WHERE V_NAME = @PREVIOUS_LOC
			
			SELECT @DB_LOTNO = V_code + @YEARLASTDIGIT + ISNULL(V_LOTNO, '001') FROM VENDOR WHERE V_NAME = @PREVIOUS_LOC
			--To check the Duplicate Lot
			IF ( SELECT COUNT(*) FROM LOT_TABLE WHERE LOT_NUMBER = @DB_LOTNO AND LOCATION_ID IN ( SELECT DISTINCT LOCATION_ID FROM LOCATION WHERE L_TYPE = 'Die Bank' ) ) > 0
			BEGIN					
					if ( @VENDOR_LOTNO < 9 )
					BEGIN
						SET @LOT_NUMBER_AS_STR = '00'+ CONVERT(VARCHAR,(@VENDOR_LOTNO + 1) )
					END
					ELSE IF (@VENDOR_LOTNO < 99 )
					BEGIN
						SET @LOT_NUMBER_AS_STR = '0'+ CONVERT(VARCHAR,(@VENDOR_LOTNO + 1) )
					END
					ELSE IF (@VENDOR_LOTNO < 999)
					BEGIN
						SET @LOT_NUMBER_AS_STR = CONVERT(VARCHAR,(@VENDOR_LOTNO + 1) )
					END
					
					SET @DB_LOTNO = @VENDOR_CODE + @YEARLASTDIGIT + @LOT_NUMBER_AS_STR
			END

			SELECT DISTINCT @DB_PROCESSSTEPID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,1) WHERE PS_TYPE_NAME = 'Die Bank' 
		END
		IF @PS_TYPE = 'SORT' 
		BEGIN
			  SELECT @GROSS_DIE = ISNULL(SHIP_GOOD_DIE_QTY,0) FROM SHIPPED_LOT WHERE SHIPPEDLOt_ID = @SHIPPEDLOT_ID
			  IF @GROSS_DIE <= 0
			  BEGIN
					SET @GROSS_DIE = @SHIP_QTY * @GORSS_DIE_PER_WAFER
			  END	
		END
		IF @PS_TYPE = 'DIE BANK' AND @SHIP_NEXT_PROCESS <> 'Assembly'
		BEGIN	
				
				INSERT INTO @WAFERS_TABLE (WAFER_ID, QTY_IN) SELECT WAFER_ID, BIN_1_TRANSIT_QTY FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_TRANSIT_QTY > 0 ORDER bY CAST(WAFER_ID AS INT)

				SELECT @WAFER_ID_STR = COALESCE( @WAFER_ID_STR + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
				 FROM @WAFERS_TABLE ORDER BY WAFER_ID 

				SELECT @QTY_AS_STRING = COALESCE( @QTY_AS_STRING + ';' + CONVERT( VARCHAR(10), QTY_IN ), CONVERT( VARCHAR(10) , QTY_IN ) )
				 FROM @WAFERS_TABLE ORDER BY WAFER_ID

				SET @DB_PROCESSSTEPID = @PROCESSTEP_ID

				SELECT @GROSS_DIE = ISNULL(SUM(BIN_1_TRANSIT_QTY), 0) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_TRANSIT_QTY > 0
								
				UPDATE #RESULTS SET WAFER_ID = @WAFER_ID_STR, SHIP_QTY = @GROSS_DIE WHERE LOT_ID = @LOT_ID AND SHIPPEDLOT_ID = @SHIPPEDLOT_ID
				
		END
		
		UPDATE #RESULTS SET GDPW = ISNULl(@GORSS_DIE_PER_WAFER,0), GROSS_DIE = @GROSS_DIE, PS_TYPE_ID = @PS_TYPE_ID, VENDOR_CODE = @VENDOR_CODE, DB_LOTNO = @DB_LOTNO,DB_PROCESSSTEP_ID = @DB_PROCESSSTEPID, BIN_QTY_STR = @QTY_AS_STRING WHERE LOT_ID = @LOT_ID AND SHIPPEDLOT_ID = @SHIPPEDLOT_ID
		
		DELETE FROM @WAFERS_TABLE
	
		SET @GORSS_DIE_PER_WAFER = NULL
		SET @GROSS_DIE = NULL
		SET @DB_LOTNO  = NULL
		SET @VENDOR_CODE = NULL
		SET @DB_PROCESSSTEPID = NULL
		SET @QTY_AS_STRING	  = NULL
		SET @WAFER_ID_STR	  = NULL
		FETCH NEXT FROM @TRANSITCURSOR INTO @PROCESSTEP_ID, @PS_TYPE, @PREVIOUS_LOC, @SHIP_QTY, @SHIPPEDLOT_ID, @LOT_ID,@SHIP_NEXT_PROCESS
		
	END
	CLOSE @TRANSITCURSOR
	DEALLOCATE @TRANSITCURSOR

	IF @PROCESS_TYPES = 'Fab,Fab Option,Die Bank'
	BEGIN
			SELECT * FROM #RESULTS WHERE SHIP_QTY > 0 AND GROSS_DIE > 0 ORDER BY PS_TYPE_ID, SHIP_DATE, PART_NUMBER DESC 
	END
	ELSE
	BEGIN
			SELECT * FROM #RESULTS WHERE SHIP_QTY > 0 ORDER BY PS_TYPE_ID, SHIP_DATE, PART_NUMBER DESC 
	END

END

